// 调试统计数据问题
const { Pool } = require('pg')

async function debugStats() {
  const pool = new Pool({
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 5432,
    database: process.env.DB_NAME || 'aide',
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASSWORD || 'password'
  })
  
  try {
    const client = await pool.connect()
    
    console.log('🔍 调试统计数据问题')
    console.log('='.repeat(50))
    
    // 1. 检查是否有任务数据
    const taskCountResult = await client.query('SELECT COUNT(*) as count FROM tasks')
    console.log(`📊 总任务数: ${taskCountResult.rows[0].count}`)
    
    // 2. 检查最近的任务
    const recentTasksResult = await client.query(`
      SELECT 
        id,
        title,
        created_at,
        updated_at,
        completed,
        SUBSTRING(created_at::text, 1, 10) as created_date
      FROM tasks 
      ORDER BY created_at DESC 
      LIMIT 5
    `)
    
    console.log('\n📋 最近的任务:')
    recentTasksResult.rows.forEach((row, index) => {
      console.log(`  ${index + 1}. ${row.title} (${row.created_date}) - ${row.completed ? '已完成' : '未完成'}`)
    })
    
    // 3. 测试时间趋势查询
    const startDate = new Date()
    startDate.setDate(startDate.getDate() - 7)
    const startDateStr = startDate.toLocaleDateString('zh-CN', {
      year: 'numeric',
      month: '2-digit',
      day: '2-digit'
    }).replace(/\//g, '-')
    
    console.log(`\n📅 测试7天趋势查询 (起始日期: ${startDateStr}):`)
    
    const trendsResult = await client.query(`
      SELECT 
        SUBSTRING(created_at::text, 1, 10) as date,
        COUNT(*) as created_count
      FROM tasks 
      WHERE SUBSTRING(created_at::text, 1, 10) >= $1
      GROUP BY SUBSTRING(created_at::text, 1, 10)
      ORDER BY date
    `, [startDateStr])
    
    console.log('创建趋势结果:', trendsResult.rows)
    
    // 4. 检查完成趋势
    const completedResult = await client.query(`
      SELECT 
        SUBSTRING(updated_at::text, 1, 10) as date,
        COUNT(*) as completed_count
      FROM tasks 
      WHERE completed = true AND SUBSTRING(updated_at::text, 1, 10) >= $1
      GROUP BY SUBSTRING(updated_at::text, 1, 10)
      ORDER BY date
    `, [startDateStr])
    
    console.log('完成趋势结果:', completedResult.rows)
    
    // 5. 检查用户ID问题
    const userResult = await client.query('SELECT DISTINCT user_id FROM tasks LIMIT 5')
    console.log('\n👥 用户ID列表:', userResult.rows.map(r => r.user_id))
    
    client.release()
    
  } catch (error) {
    console.error('调试失败:', error)
  } finally {
    await pool.end()
    process.exit(0)
  }
}

debugStats()
